#include "sqlite.h"

Sqlite::Sqlite(QString dbpath,QString dbname,QString conn_name)//相对当前exe的路径
{
//    qDebug() << QSqlDatabase::drivers();
    QString path;
    _dbname=dbname;
    _connectionName=conn_name;
    if(dbpath==""){
        path=QDir::currentPath();
    }else{
        path=QDir::currentPath()+"/"+dbpath;
    }
    db = QSqlDatabase::addDatabase("QSQLITE");
    QDir dir;
    if(!dir.exists(path)){
        dir.mkpath(path);
    }
    QString file = path+"/"+_dbname+".db";//qDebug()<<file;
    db.setDatabaseName(file);
    if(!db.open()){
        QMessageBox::critical(NULL, "警告", "数据库连接失败，请检查sqlite服务", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
        exit(-1);//退出软件
    }else{
        qDebug()<<"sqlite open ok";
    }
}

Sqlite::~Sqlite()
{
    db.close();
}

bool Sqlite::createTable(QString table, QString content)
{
    bool ret=true;
    if(db.isOpen()){
        QSqlQuery query(QSqlDatabase::database(_connectionName));
        QString sql = QString("CREATE TABLE IF NOT EXISTS "+table+"(");
        sql+=content;
        sql+=")";
        setLastSqlString(sql);//记录sql语句;
        if(!query.exec(sql)){
            setLastError("表"+table+"创建失败");
            ret=false;
        }
    }else{
        setLastError("数据库未打开");
    }
    return ret;
}

bool Sqlite::dropTable(QString table)
{
    bool ret=true;
    if(db.isOpen()){
        QSqlQuery query(QSqlDatabase::database(_connectionName));
        QString sql = QString("DROP TABLE "+table);
        setLastSqlString(sql);//记录sql语句;
        if(!query.exec(sql)){
            setLastError("表"+table+"删除失败,或不存在");
            ret=false;
        }
    }else{
        setLastError("数据库未打开");
    }
    return ret;
}

void Sqlite::close()
{
    if(db.isOpen()){
        db.close();
    }
}

bool Sqlite::insertInto(QString table, QVariantMap dat)
{
    bool ret=true;
    if(db.isOpen()){
        QSqlQuery query(QSqlDatabase::database(_connectionName));
        QString sql = QString("INSERT INTO "+table+"(");

        QVariantMap::Iterator it=dat.begin();
        while(it!=dat.end())
        {
            sql+=it.key();
            if(it.key()!=dat.lastKey())
            {
                sql+=",";
            }
            it++;
        }
        sql+=") VALUES(";
        it=dat.begin();
        while(it!=dat.end())
        {
            if(it.value().type()==QVariant::Int){
                sql+=QString("%1").arg(it.value().toInt());
            }else if(it.value().type()==QVariant::Double){
                sql+=QString("%1").arg(it.value().toDouble());
            }else if(it.value().type()==QVariant::String){
                sql+=it.value().toString();
            }
            if(it.key()!=dat.lastKey())
            {
                sql+=",";
            }
            it++;
        }
        sql+=")";
        setLastSqlString(sql);//记录sql语句;
        if(!query.exec(sql)){
            setLastError("表"+table+"通过id删除数据失败");
            ret = false;
        }
    }else{
        setLastError("数据库未打开 "+db.lastError().text());

        ret = false;
    }
    return ret;
}

bool Sqlite::deleteBy(QString table, QString condition)
{
    bool ret=true;
    if(db.isOpen()){
        QSqlQuery query(QSqlDatabase::database(_connectionName));
        QString sql = QString("delete from "+table+" where "+condition);
        if(!query.exec(sql)){
            setLastError("表"+table+"通过条件删除数据失败");
            ret = false;
        }
        setLastSqlString(sql+" 受影响行数"+query.numRowsAffected());//记录sql语句;
    }else{
        setLastError("数据库未打开");
        ret = false;
    }

    return ret;
}

bool Sqlite::updateBy(QString table, QString condition, QVariantMap dat)
{
    bool ret=true;
    if(db.isOpen()){
        QSqlQuery query(QSqlDatabase::database(_connectionName));
        QString sql = QString("UPDATE "+table+" SET ");
        QVariantMap::Iterator it=dat.begin();
        while(it!=dat.end())
        {
            sql+=it.key();
            if(it.value().type()==QVariant::Int){
                sql+=QString("=%1").arg(it.value().toInt());
            }else if(it.value().type()==QVariant::Double){
                sql+=QString("=%1").arg(it.value().toDouble());
            }else if(it.value().type()==QVariant::String){
                sql+="='";
                sql+=it.value().toString();
                sql+="'";
            }
            if(it.key()!=dat.lastKey())
            {
                sql+=",";
            }
            it++;
        }
        sql+=" WHERE ";
        sql+=condition;
        setLastSqlString(sql);//记录sql语句;
        if(!query.exec(sql)){
            setLastError("表"+table+"通过条件更新失败");
            ret = false;
        }
    }else{
        setLastError("数据库未打开 ");
        ret = false;
    }
    return ret;
}

//content 例: "id,stu_num,height"//分别对应的是int  int double类型的三个字段
//外部调用该函数时候，返回结果之后再外部遍历即可得到相应字段的数据
//while (query.next())
//{
//    qDebug()<< query.value(0).toInt();
//    qDebug()<< query.value(1).toInt();
//    qDebug()<< query.value(2).toDouble();
//}
QSqlQuery Sqlite::selectBy(QString table, QString content, QString condition)
{
    QSqlQuery query(QSqlDatabase::database(_connectionName));
    if(db.isOpen()){
        QString sql = QString("SELECT "+content+" FROM "+table);
        if(condition!=""){
            sql+=+" WHERE "+condition;
        }
        setLastSqlString(sql);//记录sql语句;
        if(!query.exec(sql)){
            setLastError("表"+table+"查询失败");
        }else{
            return query;
        }
    }else{
        setLastError("数据库未打开 ");
    }
    return query;
}

QString Sqlite::lastError() const
{
    return _lastError;
}

void Sqlite::setLastError(const QString &lastError)
{
    _lastError = lastError;
}

QString Sqlite::lastSqlString() const
{
    return _lastSqlString;
}

void Sqlite::setLastSqlString(const QString &lastSqlString)
{
    _lastSqlString = lastSqlString;
}
